package org.example.controller;

import com.alibaba.fastjson2.JSONArray;
import com.alibaba.fastjson2.JSONObject;
import org.apache.rocketmq.spring.core.RocketMQTemplate;
import org.example.service.StockService;
import org.example.utils.Result;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.messaging.Message;
import org.springframework.messaging.support.MessageBuilder;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.net.HttpURLConnection;
import java.net.URL;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

@RestController
@RequestMapping("/timeSql")
public class TimeSqlController {

    private static final String DB_HOST = "10.39.7.21";
    private static final String DB_USER = "root";
    private static final String DB_PASS = "123456";
    private static final String DB_NAME = "trade";
    @Autowired
    private StockService stockService;
    @Autowired
    private RedisTemplate redisTemplate;
    @Autowired
    private RocketMQTemplate rocketMqTemplate;

    private static Connection connectToDatabase() throws SQLException {
        return DriverManager.getConnection("jdbc:mysql://" + DB_HOST + "/" + DB_NAME, DB_USER, DB_PASS);
    }

    private static void createTable(Connection connection) throws SQLException {
        String sql = "TRUNCATE TABLE tb_stock";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.executeUpdate();

        System.out.println("表 tb_stock 已截断");
    }

    private static JSONObject fetchStockData(int page) throws IOException {
        URL url = new URL("http://81.push2.eastmoney.com/api/qt/clist/get?pn=" + page + "&pz=20&po=1&np=1&ut=bd1d9ddb04089700cf9c27f6f7426281&fltt=2&invt=2&dect=1&wbp2u=^|0^|0^|0^|web&fid=f3&fs=m:0+t:81+s:2048&fields=f1,f2,f3,f4,f5,f6,f7,f8,f9,f10,f12,f13,f14,f15,f16,f17,f18,f20,f21,f23,f24,f25,f22,f11,f62,f128,f136,f115,f152");
        HttpURLConnection connection = (HttpURLConnection) url.openConnection();
        connection.setRequestMethod("GET");

        int responseCode = connection.getResponseCode();
        if (responseCode != 200) {
            throw new IOException("HTTP 请求失败，响应代码: " + responseCode);
        }

        BufferedReader reader = new BufferedReader(new InputStreamReader(connection.getInputStream()));
        StringBuilder response = new StringBuilder();
        String line;
        while ((line = reader.readLine()) != null) {
            response.append(line);
        }
        reader.close();

        return JSONObject.parseObject(response.toString());
    }

    private static void insertStockData(Connection connection, JSONObject stock) throws SQLException {
        String sql = "INSERT INTO tb_stock (stock_code, stock_name, latest_price, range_num, price_limit, bid_price, selling_price, yesterday_price, today_price, today_maxprice, today_minprice) VALUES (?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, stock.getString("f12"));
        statement.setString(2, stock.getString("f14"));
        statement.setDouble(3, stock.getDouble("f2"));
        statement.setDouble(4, stock.getDouble("f4"));
        statement.setDouble(5, stock.getDouble("f3"));
        statement.setDouble(6, stock.getDouble("f23"));
        statement.setDouble(7, stock.getDouble("f9"));
        statement.setDouble(8, stock.getDouble("f18"));
        statement.setDouble(9, stock.getDouble("f17"));
        statement.setDouble(10, stock.getDouble("f15"));
        statement.setDouble(11, stock.getDouble("f16"));
        statement.executeUpdate();
        System.out.println(stock.getString("f14") + " 数据插入成功！");

    }

    @RequestMapping("/updateSql")
    public Result updateSql() {
        // 连接数据库
        try (Connection connection = connectToDatabase()) {
            System.out.println("数据库连接成功!");

            // 创建表
            createTable(connection);

            // 获取股票数据
            for (int page = 1; page <= 13; page++) {
                JSONObject stockData = fetchStockData(page);

                // 解析股票数据
                JSONArray data = null;
                try {
                    if (stockData != null) {
                        data = stockData.getJSONObject("data").getJSONArray("diff");
                    } else {
                        // 处理 stockData 为 null 的情况，例如打印错误信息或采取其他适当的措施
                        System.out.println("Stock data is null.");
                        return Result.fail();
                    }
                } catch (Exception e) {
                    throw new RuntimeException(e);
                }
                Boolean delete = redisTemplate.delete("StockList");
                System.out.println("redis里面列表已经不存在：" + delete);
                // 插入股票数据到数据库
                for (int i = 0; i < data.size(); i++) {
                    JSONObject stock = data.getJSONObject(i);
                    insertStockData(connection, stock);
                }


//        发送rocketmq 消息进行延时双删
                Message<String> message = MessageBuilder.withPayload("111").build();
                rocketMqTemplate.syncSend("delStockList", message, 1500, 1);

            }
            // 抓取完数据后结束程序
            return Result.success();
        } catch (SQLException | IOException e) {
            System.out.println("数据库连接失败: " + e.getMessage());
            return Result.fail();
        }
    }

}
